﻿Public Class ThisAddIn

    Private tp As TaskPane
    Private cpt As Microsoft.Office.Tools.CustomTaskPane

    Private isInit As Boolean = False

    Public Sub Application_NewWorkbook(Wb As Microsoft.Office.Interop.Excel.Workbook) Handles Application.NewWorkbook

        If Application.Windows.Count = 1 And isInit = False Then
            InitCtp(Application.Windows.Item(1))
            AddHandler Globals.ThisAddIn.Application.WorkbookOpen, AddressOf WorkbookOpen
            AddHandler Globals.ThisAddIn.Application.WindowActivate, AddressOf WindowActivate
            isInit = True
        End If

    End Sub

    Private Sub InitCtp(wn As Excel.Window)

        Dim found = False

        Dim ignorable = "The taskpane has been deleted or is otherwise no longer valid."

        For i = Me.CustomTaskPanes.Count - 1 To 0 Step -1
            Try
                If Me.CustomTaskPanes.Item(i).Title = "SQL To Excel Task Pane" Then
                    Try
                        If CType(Me.CustomTaskPanes.Item(i).Window, Excel.Window).Hwnd = wn.Hwnd Then
                            found = True
                        End If
                    Catch ex As Runtime.InteropServices.COMException When ex.Message = ignorable
                    End Try
                End If
            Catch ex As Runtime.InteropServices.COMException When ex.Message = ignorable
            End Try
        Next

        If found = False Then

            tp = New TaskPane

            AddHandler tp.MakeTable, AddressOf tp_MakeTable
            AddHandler tp.MakePivot, AddressOf tp_MakePivot

            AddHandler tp.CopyScript, AddressOf tp_CopyScript
            AddHandler tp.SaveAsUnique, AddressOf tp_SaveAsUnique
            AddHandler tp.SaveAsCsv, AddressOf tp_SaveAsCsv

            AddHandler tp.ForgetSqlServerName, AddressOf tp_ForgetSqlServerName

            cpt = Me.CustomTaskPanes.Add(tp, "SQL To Excel Task Pane")
            cpt.Visible = True

        End If

    End Sub

    Private Sub WorkbookOpen(wb As Excel.Workbook) Handles Application.WorkbookOpen

        For Each wn As Excel.Window In wb.Windows
            InitCtp(wn)
        Next

    End Sub

    Private Sub WindowActivate(wb As Excel.Workbook, wn As Excel.Window) Handles Application.WindowActivate

        InitCtp(wn)

    End Sub

    Private ReadOnly Property TheActiveWorkbook() As Excel.Workbook
        Get

            If Globals.ThisAddIn.Application.ActiveWorkbook Is Nothing Then
                Globals.ThisAddIn.Application.Workbooks.Add()
            End If

            Return Globals.ThisAddIn.Application.ActiveWorkbook

        End Get
    End Property

    Private ReadOnly Property TheActiveWorksheet() As Excel.Worksheet
        Get
            Return CType(TheActiveWorkbook.ActiveSheet, Excel.Worksheet)
        End Get
    End Property

    Private ReadOnly Property TheSelection() As Excel.Range
        Get

            If CType(Globals.ThisAddIn.Application.Selection, Excel.Range).Areas.Count = 1 Then
                Return CType(Globals.ThisAddIn.Application.Selection, Excel.Range).Areas(1)
            Else
                Return Nothing
            End If

        End Get
    End Property


#Region "Event Handlers"


    Private Sub tp_MakeTable(ByVal sender As Object, ByVal e As System.EventArgs)
        ExcelHelper.FillList(TheActiveWorksheet, CStr(sender))
    End Sub

    Private Sub tp_MakePivot(ByVal sender As Object, ByVal e As System.EventArgs)
        ExcelHelper.fillPivot(TheActiveWorkbook, CStr(sender))
    End Sub

    Private Sub tp_SaveAsUnique(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim path = String.Format("{0}\{1}.xlsx", My.Computer.FileSystem.SpecialDirectories.MyDocuments, Guid.NewGuid.ToString)
        TheActiveWorkbook.SaveAs(Filename:=path, FileFormat:=Excel.XlFileFormat.xlWorkbookDefault)

    End Sub

    Private Sub tp_SaveAsCsv(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim path = String.Format("{0}\{1}.csv", My.Computer.FileSystem.SpecialDirectories.Desktop, Guid.NewGuid.ToString)
        TheActiveWorkbook.SaveAs(Filename:=path, FileFormat:=Excel.XlFileFormat.xlCSV)

    End Sub

    Private Sub tp_CopyScript(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim range = DirectCast(Application.Selection, Excel.Range)
        Dim script = ExcelToSql.Script(range)
        My.Computer.Clipboard.SetText(script)

    End Sub

    Private Sub tp_ForgetSqlServerName(ByVal sender As Object, ByVal e As System.EventArgs)
        My.Settings.Properties("ConnectionString").DefaultValue = String.Empty
        My.Settings.Save()
    End Sub

#End Region



End Class
